package com.lm.ledger.dao;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lm.ledger.utill.Account;
import com.opensymphony.xwork2.Preparable;

public class Test {

	/**
	 * @param args
	 */
	
	PreparedStatement ps;
	ResultSet rs;
	public static void main(String[] args) {
		/*String formId = "ABC1";  		//A
		String locId = "1" ;		//B
		String trxDate = null ;		//C
		String trxType = "Debit" ;		//D
		String status = null;		//E
*/		Test test = new Test();
		//String q = test.getQuery(formId,locId,trxDate,trxType,status);
		//System.out.println(q);
		test.searchList1(new Account());
	}
	public List searchList1(Account account1){
		List trxList = new ArrayList();
		Account account;
		String formId = account1.getFormId();
		String locId = account1.getLocId();
		Double amount = account1.getAmount();
		String trxDate = account1.getTxDate();
		String trxType = account1.getTxTye();
		String authId = account1.getAuthId();
		String status = account1.getStatus();
		/*String formId = "ABC1";  		//A
		String locId = "1" ;		//B
		String trxDate = "2011-11-24" ;		//C
		String trxType = "Debit" ;		//D
		String status = "true";		//E
*/		System.out.println("search List......");
		String query = getQuery(formId,locId,trxDate,trxType,status);;
		
		
		try {
			Connection conn = ConnMngt1.getConnection();
			ps = conn.prepareStatement(query);
			
			if(formId != null && locId !=null && trxDate !=null && trxType !=null && status !=null){
				// where = "and formId=? and locId=? and trxDate=? and trxType=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, trxDate);
				ps.setString(4, trxType);
				ps.setString(5, status);
			}else if(formId != null && locId !=null && trxDate !=null && trxType !=null){
				// where = "and formId=? and locId=? and trxDate=? and trxType=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, trxDate);
				ps.setString(4, trxType);
			}else if(formId != null && locId !=null && trxType !=null && status !=null){
				// where = "and formId=? and locId=? and trxType=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, trxType);
				ps.setString(4, status);
			}else if(formId != null && trxDate !=null && trxType !=null && status !=null){
				// where = "and formId=? and trxDate=? and trxType=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, trxDate);
				ps.setString(3, trxType);
				ps.setString(4, status);
			}else if(formId != null && locId !=null && trxDate !=null && status !=null){
				// where = "and formId=? and locId=? and trxDate=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, trxDate);
				ps.setString(4, status);
			}else if(locId !=null && trxDate !=null && trxType !=null && status !=null){
				// where = "and locId=? and trxDate=? and trxType=? and status=?";
				ps.setString(1, locId);
				ps.setString(2, trxDate);
				ps.setString(3, trxType);
				ps.setString(4, status);
			}else if(formId != null && locId !=null && trxDate !=null){
				// where = "and formId=? and locId=? and trxDate=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, trxDate);
			}else if(formId != null && locId !=null && trxType !=null){
				// where = "and formId=? and locId=? and trxType=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, trxType);
			}else if(formId != null && locId !=null && status !=null){
				// where = "and formId=? and locId=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, locId);
				ps.setString(3, status);
			}else if(formId != null && trxDate !=null && status !=null){
				// where = "and formId=? and trxDate=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, trxDate);
				ps.setString(3, status);
			}else if(formId !=null && trxDate !=null && trxType !=null){
				// where = "and formId=? and trxDate=? and trxType=?";
				ps.setString(1, formId);
				ps.setString(2, trxDate);
				ps.setString(3, trxType);
			}else if(formId != null && trxType !=null && status !=null){
				// where = "and formId=? and trxType=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, trxType);
				ps.setString(3, status);
			}else if(locId !=null && trxDate !=null && trxType !=null){
				// where = "and locId=? and trxDate=? and trxType=?";
				ps.setString(1, locId);
				ps.setString(2, trxDate);
				ps.setString(3, trxType);
			}else if(locId !=null && trxDate !=null && status !=null){
				// where = "and locId=? and trxDate=? and status=?";
				ps.setString(1, locId);
				ps.setString(2, trxDate);
				ps.setString(3, status);
			}else if(trxDate !=null && trxType !=null && status !=null){
				// where = "and trxDate=? and trxType=? and status=?";
				ps.setString(1, trxDate);
				ps.setString(2, trxType);
				ps.setString(3, status);
			}else if(formId != null && locId !=null){
				// where = "and formId=? and locId=?";
				System.out.println("formId != null && locId !=null");
				ps.setString(1, formId);
				ps.setString(2, locId);
			}else if(trxType !=null && status !=null){
				// where = "and trxType=? and status=?";
				ps.setString(1, trxType);
				ps.setString(2, status);
			}else if(formId != null && trxDate !=null){
				// where = "and formId=? and trxDate=?";
				ps.setString(1, formId);
				ps.setString(2, trxDate);
			}else if(formId != null&& trxType !=null){
				// where = "and formId=? and trxType=?";
				ps.setString(1, formId);
				ps.setString(2, trxType);
			}else if(formId != null && status !=null){
				// where = "and formId=? and status=?";
				ps.setString(1, formId);
				ps.setString(2, status);
			}else if(locId !=null && trxDate !=null){
				// where = "and locId=? and trxDate=?";
				ps.setString(1, locId);
				ps.setString(2, trxDate);
			}else if(locId !=null && trxType !=null){
				// where = "and locId=? and trxType=?";
				ps.setString(1, locId);
				ps.setString(2, trxType);
			}else if(locId !=null && status !=null){
				// where = "and locId=? and status=?";
				ps.setString(1, locId);
				ps.setString(2, status);
			}else if(trxDate !=null && trxType !=null){
				// where = " and trxDate=? and trxType=?";
				ps.setString(1, trxDate);
				ps.setString(2, trxType);
			}else if(trxDate !=null && status !=null){
				// where = "and trxDate=? and status=?";
				ps.setString(1, trxDate);
				ps.setString(2, status);
			}else if(formId != null){
				// where = "and formId=?";
				System.out.println("formId != null");
				ps.setString(1, formId);
			}else if(status != null){
				// where = "and status=?";
				ps.setString(1, status);
			}else if(locId != null){
				// where = "and locId=?";
				ps.setString(1, locId);
			}else if(trxDate != null){
				// where = "and trxDate=?";
				ps.setString(1, trxDate);
			}else if(trxType != null){
				// where = "and trxType=?";
				ps.setString(1, trxType);
			}
						
			rs = ps.executeQuery();
			 while(rs.next()){
				 account = new Account();	
				 System.out.println(rs.getString(1));
				 System.out.println(rs.getString(2));
				 System.out.println(rs.getString(3));
				 System.out.println(rs.getDouble(4));
				 System.out.println(rs.getString(5));
				 System.out.println(rs.getString(6));
				 System.out.println(rs.getString(7));
				 System.out.println(rs.getString(8));
				 System.out.println(rs.getDouble(9));
				 System.out.println(rs.getString(10));
				 System.out.println(rs.getString(11));
				 System.out.println(rs.getString(12));
				 System.out.println(rs.getString(13));
				 System.out.println(rs.getString(14));
				 System.out.println(rs.getString(15));
				 System.out.println(rs.getString(16));
				 System.out.println(rs.getString(17));
				 
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));	
				 account.setAuthName(rs.getString(17));
				 trxList.add(account);
			 }		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return trxList;
	}
	private String getWhereClouse(String formId,String locId,String trxDate,String trxType,String status){
		/*
		String formId = "ABC1";  		//A
		String locId = null ;		//B
		String trxDate = null ;		//C
		String trxType = null ;		//D
		String status = null;		//E
		*/
		String where = "";
		if(formId != null && locId !=null && trxDate !=null && trxType !=null && status !=null){
			// System.out.println("formId != null && locId !=null && trxDate !=null && trxType !=null && status !=null");
			where = "and formId=? and account.LocationID=? and TransactionDate=? and TransactionType=? and account.status=?";
		}
		else if(formId != null && locId !=null && trxDate !=null && trxType !=null){
			// System.out.println("formId != null && locId !=null && trxDate !=null && trxType !=null");
			where = "and formId=? and account.LocationID=? and TransactionDate=? and TransactionType=?";
		}else if(formId != null && locId !=null && trxType !=null && status !=null){
			// System.out.println("trxDate id is null");
			where = "and formId=? and account.LocationID=? and TransactionType=? and account.status=?";
		}else if(formId != null && trxDate !=null && trxType !=null && status !=null){
			// System.out.println("trxType id is null");
			where = "and formId=? and TransactionDate=? and TransactionType=? and account.status=?";
		}else if(formId != null && locId !=null && trxDate !=null && status !=null){
			// System.out.println("gorm id is blank");
			where = "and formId=? and account.LocationID=? and TransactionDate=? and account.status=?";
		}else if(locId !=null && trxDate !=null && trxType !=null && status !=null){
			// System.out.println("form id is null");
			where = "and account.LocationID=? and TransactionDate=? and TransactionType=? and account.status=?";
		}
		else if(formId != null && locId !=null && trxDate !=null){
			// System.out.println("locId id is null");
			where = "and formId=? and account.LocationID=? and TransactionDate=?";
		}else if(formId != null && locId !=null && trxType !=null){
			// System.out.println("trxDate id is null");
			where = "and formId=? and account.LocationID=? and TransactionType=?";
		}else if(formId != null && locId !=null && status !=null){
			// System.out.println("form id is null");
			where = "and formId=? and account.LocationID=? and account.status=?";
		}else if(formId != null && trxDate !=null && status !=null){
			// System.out.println("locId id is null");
			where = "and formId=? and TransactionDate=? and account.status=?";
		}else if(formId !=null && trxDate !=null && trxType !=null){
			// System.out.println("form id is null");
			where = "and formId=? and TransactionDate=? and TransactionType=?";
		}else if(formId != null && trxType !=null && status !=null){
			// System.out.println("trxDate id is null");
			where = "and formId=? and TransactionType=? and account.status=?";
		}else if(locId !=null && trxDate !=null && trxType !=null){
			// System.out.println("form id is null");
			where = "and account.LocationID=? and TransactionDate=? and TransactionType=?";
		}else if(locId !=null && trxDate !=null && status !=null){
			// System.out.println("locId id is null");
			where = "and account.LocationID=? and TransactionDate=? and account.status=?";
		}else if(trxDate !=null && trxType !=null && status !=null){
			// System.out.println("TransactionDate id is null");
			where = "and TransactionDate=? and TransactionType=? and account.status=?";
		}
		else if(formId != null && locId !=null){
			// System.out.println("locId id is null");
			where = "and formId=? and account.LocationID=?";
		}else if(trxType !=null && status !=null){
			// System.out.println("trxDate id is null");
			where = "and TransactionType=? and account.status=?";
		}else if(formId != null && trxDate !=null){
			// System.out.println("form id is null");
			where = "and formId=? and TransactionDate=?";
		}else if(formId != null&& trxType !=null){
			// System.out.println("locId id is null");
			where = "and formId=? and TransactionType=?";
		}else if(formId != null && status !=null){
			// System.out.println("trxDate id is null");
			where = "and formId=? and account.status=?";
		}else if(locId !=null && trxDate !=null){
			// System.out.println("form id is null");
			where = "and account.LocationID=? and TransactionDate=?";
		}else if(locId !=null && trxType !=null){
			// System.out.println("locId id is null");
			where = "and account.LocationID=? and TransactionType=?";
		}else if(locId !=null && status !=null){
			// System.out.println("trxDate id is null");
			where = "and account.LocationID=? and account.status=?";
		}else if(trxDate !=null && trxType !=null){
			// System.out.println("form id is null");
			where = " and TransactionDate=? and TransactionType=?";
		}else if(trxDate !=null && status !=null){
			// System.out.println("locId id is null");
			where = "and TransactionDate=? and account.status=?";
		}
		else if(formId != null){
			// System.out.println("trxDate id is null");
			where = "and formId=?";
		}else if(status != null){
			// System.out.println("form id is null");
			where = "and account.status=?";
		}else if(locId != null){
			// System.out.println("locId id is null");
			where = "and account.LocationID=?";
		}else if(trxDate != null){
			// System.out.println("trxDate id is null");
			where = "and TransactionDate=?";
		}else if(trxType != null){
			// System.out.println("trxDate id is null");
			where = "and TransactionType=?";
		}
		
		return where;
	}
	public String getQuery(String formId,String locId,String trxDate,String trxType,String status){
				
		String query2=getWhereClouse(formId,locId,trxDate,trxType,status);
	
		String query1 = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID  ";
		
		String query = query1+query2;
		System.out.println(query);
		return query;
	}
}
